package com.eorchis.module.classcourse.dao.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.eorchis.core.basedao.dao.IDaoSupport.QueryStringType;
import com.eorchis.core.basedao.dao.impl.HibernateAbstractBaseDao;
import com.eorchis.core.basedao.entity.IBaseEntity;
import com.eorchis.core.basedao.query.condition.IPagingQueryCondition;
import com.eorchis.core.basedao.query.condition.builder.CompareType;
import com.eorchis.core.basedao.query.condition.builder.IConditionBuilder;
import com.eorchis.core.basedao.query.condition.builder.impl.DefaultQueryConditionBuilder;
import com.eorchis.core.page.commond.SortInfoBean;
import com.eorchis.core.ui.commond.IQueryCommond;
import com.eorchis.module.classcourse.dao.IClassCourseDao;
import com.eorchis.module.classcourse.domain.ClassCourse;
import com.eorchis.module.classcourse.querybean.ClassCourseQueryBean;
import com.eorchis.module.classcourse.ui.commond.ClassCourseQueryCommond;
import com.eorchis.module.classcourse.ui.commond.ClassCourseValidCommond;
import com.eorchis.utils.utils.PropertyUtil;

/**
 * Title: ClassCourseDaoImpl<br>
 * Description: 班级课程维护数据接口实现<br>
 * Company: GoldGov<br>
 * Copyright @ 2012 GoldGov .All rights reserved.<br>
 * @author Max
 * @createDate 2014-12-11
 * @version $Revision$
 */
@SuppressWarnings("unchecked")
@Repository(value = "com.eorchis.module.classcourse.dao.impl.ClassCourseDaoImpl")
public class ClassCourseDaoImpl
					extends HibernateAbstractBaseDao
					implements IClassCourseDao {
	
	public Class<? extends IBaseEntity> entityClass() {
		return ClassCourse.class;
	}
	
	public void queryConditionProcessor(
			IConditionBuilder defaultQueryConditionBuilder,
			IQueryCommond queryCommond) {
		ClassCourseQueryCommond classCourseQueryCommond = (ClassCourseQueryCommond)queryCommond;
		defaultQueryConditionBuilder.setBaseQueryString("SELECT t FROM ClassCourse t");
		defaultQueryConditionBuilder.addCondition("t.classCourseId", CompareType.IN, classCourseQueryCommond.getSearchClassCourseIds());
		defaultQueryConditionBuilder.addCondition("t.classCourseId", CompareType.EQUAL,classCourseQueryCommond.getSearchClassCourseId());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.HQL);
	}
	
	
	
	/**
	 * 查询班级课程列表
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @date 2014-12-11
	 */
	@Override
	public List<ClassCourseQueryBean> findClassCoursePageList(ClassCourseQueryCommond 
			qCommond) throws Exception {
		StringBuffer queryHql = new StringBuffer();
		//拼写查询的sql
		StringBuffer hql = new StringBuffer();
		hql.append("select t.classCourseId as classCourseId,t.courseName as courseName,t.courseNum as courseNum," +
				"t.speaker as speaker,t.speakdept as speakdept,t.speakDuty as speakDuty,s.dataName as speakwayStr," +
				"s.dataCode as speakwayCode,t.speakDate as speakDate,c.dataName as speakTimeStr,c.dataCode as speakTimeCode");
		hql.append(" FROM ClassCourse t left join t.speakway s left join t.speakTime c where 1=1");
		//查询数量的sql	
		StringBuffer countHql = new StringBuffer("FROM ClassCourse t where 1=1 ");
		//拼写查询条件sql begin
		Map<String, Object> values = new HashMap<String, Object>();
		//班级id
		if(PropertyUtil.objectNotEmpty(qCommond.getSearchClassId())){
			queryHql.append(" and t.trainingClass.classId = :classId ");
			values.put("classId", qCommond.getSearchClassId().trim());
		}
		//拼写查询条件sql end
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder
				.setResultObject(ClassCourseQueryBean.class);
		//查询班级数量
		countHql.append(queryHql);
		defaultQueryConditionBuilder.setBaseQueryString(countHql.toString());
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		//查询总数量
		Long count = this.count(queryCondition);
		//
		int firstResult = qCommond.calculate(count.longValue());

		// 数据列表查询
		hql.append(queryHql);
		
		//拼写排序的sql begin
		List<SortInfoBean> sortInfoList = qCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			hql.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
		} else {
			hql.append(" order by t.speakDate desc, t.speakTime, t.classCourseId desc");
		}
		//拼写排序的sql end
		defaultQueryConditionBuilder.setBaseQueryString(hql.toString());
		IPagingQueryCondition queryCondition1 = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition1.setParameters(values);
		
		List<ClassCourseQueryBean> list=(List<ClassCourseQueryBean>) this.findList(queryCondition1,
				firstResult, qCommond.getLimit());
		return list;
	}
	
	
	/**
	 * 查询班级课程列表
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @date 2014-12-11
	 */
	@Override
	public List<ClassCourseQueryBean> findClassCourseAllList(ClassCourseQueryCommond 
			qCommond) throws Exception {
		//拼写查询的sql
		StringBuffer hql = new StringBuffer();
		hql.append("select t.classCourseId as classCourseId,t.courseName as courseName,t.courseNum as courseNum,t.speaker as speaker," +
				"t.speakdept as speakdept,s.dataName as speakwayStr,s.dataCode as speakwayCode,t.speakDate as speakDate,t.speakDuty as speakDuty,");
		hql.append("c.dataName as speakTimeStr,c.dataCode as speakTimeCode FROM ClassCourse t left join t.speakway s left join t.speakTime c where 1=1");
		//拼写查询条件sql begin
		Map<String, Object> values = new HashMap<String, Object>();
		//班级id
		if(PropertyUtil.objectNotEmpty(qCommond.getSearchClassId())){
			hql.append(" and t.trainingClass.classId = :classId ");
			values.put("classId", qCommond.getSearchClassId().trim());
		}
		
		List<SortInfoBean> sortInfoList = qCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			hql.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
		} else {
			hql.append(" order by t.speakDate desc, t.classCourseId");
		}
		
		return (List<ClassCourseQueryBean>) this.executeFind(QueryStringType.HQL, hql.toString(), values, ClassCourseQueryBean.class);
	}
	
	/**
	 * 查询班级课程列表数量
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @date 2014-12-11
	 */
	@Override
	public List<ClassCourseQueryBean> findCourseNum(ClassCourseQueryCommond 
			qCommond) throws Exception {
		Map<String, Object> values = new HashMap<String, Object>();
		String hql="select count(c.classCourseId) as courseCountNum,c.trainingClass.classId as classId from ClassCourse c where c.trainingClass.classId in(:classIds) group by c.trainingClass.classId";
		values.put("classIds", qCommond.getSearchClassIds());
		return (List<ClassCourseQueryBean>) this.executeFind(QueryStringType.HQL,hql,values, ClassCourseQueryBean.class);
	}

	
	/**
	 * 获取这一年中培训班课程最大的编号
	 * @throws Exception
	 * @author Max
	 * @createDate 2014-12-12
	 */
	@Override
	public String getMaxCourseNum(ClassCourseValidCommond vCommond) throws Exception{
		Map<String, Object> values=new HashMap<String, Object>();
		String hql="select max(tc.courseNum) as courseNum from ClassCourse tc where tc.courseNum like :courseNum and tc.courseNum is not null";
		values.put("courseNum", "%"+vCommond.getSearchCourseNum()+"%");
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(hql.toString());
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		List<Object> result= (List<Object>) this.findList(queryCondition);
		if(result.get(0)!=null) {
			return result.get(0).toString();
		}else {
			return "empty";
		}
	}
	
	
	/**
	 * 获取课程中的学时总数
	 * @throws Exception
	 * @author Max
	 * @createDate 2014-12-12
	 */
	@Override
	public Double getstudyScore(String [] ids) throws Exception{
		Map<String, Object> values=new HashMap<String, Object>();
		String hql="select sum(tc.studyScore) as studyScore from ClassCourse tc where tc.classCourseId in(:classCourseId)";
		values.put("classCourseId", ids);
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(hql.toString());
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		List<Object> result= (List<Object>) this.findList(queryCondition);
			return (Double) result.get(0);
	}
	
	/**
	 * 删除课程
	 * @param classIds
	 * @throws Exception
	 * @author Max
	 * @date 2015-1-7
	 */
	@Override
	public void deleteCourseByClassIds(	String [] classIds)throws Exception {
		Map<String, Object> values=new HashMap<String, Object>();
		StringBuffer buffer = new StringBuffer("delete ClassCourse tc where tc.trainingClass.classId in (:searchUpdateClassIds)");
		values.put("searchUpdateClassIds", classIds);
		this.executeUpdate(QueryStringType.HQL, buffer.toString(), values);
	}
	
	/**
	 * 查询班级课程列表
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author RongSL
	 * @date 2016-05-20
	 */
	@Override
	public List<ClassCourseQueryBean> findAssessCourseList(ClassCourseQueryCommond  qCommond) throws Exception {
		//拼写查询条件sql begin
		Map<String, Object> values = new HashMap<String, Object>();
		//拼写查询的sql
		StringBuffer sql = new StringBuffer();
		sql.append("select * from (select tcc.CLASS_COURSE_ID as \"classCourseId\", " +
				"tcc.COURSE_NAME     as \"courseName\", " +
				"tcc.STUDY_SCORE     as \"studyScore\", " +
				"tcc.COURSE_NUM      as \"courseNum\", " +
				"tcc.DUTY_REMARK     as \"dutyRemark\", " +
				"tcc.SPEAKDEPT       as \"speakdept\", " +
				"bb.DATA_NAME         as \"speakwayStr\", " +
				"bb.DATA_CODE         as \"speakwayCode\", " +
				"tcc.CONTENT         as \"content\", " +
				"tcc.SPEAKER         as \"speaker\", " +
				"case ( SELECT count(1) FROM questionnaire_user_answer qua WHERE qua.class_id = tcc.class_course_id "
				+ "AND qua.questionnaire_id = q.questionnaire_id AND qua.userid =:userId ) when 0 then 2 else 1 end as \"CourseAnswerState\"," +
				"ifnull(tcc.course_assess_start_time,tc.end_date-1) as \"assessCourseStartDateStr\", " +
				"ifnull(tcc.course_assess_end_time,tc.end_date+2) as \"assessCourseEndDateStr\"," +
				"case when q.questionnaire_name is null then 2 else 1 end as \"questionnaireState\"," +
				"ifnull(q.questionnaire_name,'未设置')   as \"questionnaireName\"  " +
				"from TRAINING_CLASS_COURSE tcc  " +
				"left outer join BASE_BASEDATA bb on tcc.SPEAKWAY = bb.DATA_CODE " +
				"left join questionnaire_survey_object qso on tcc.class_course_id=qso.survey_object_id " +
				"left join questionnaire q on qso.questionnaire_id=q.questionnaire_id " +
				"left join training_class tc  on tcc.class_id=tc.class_id where 1 = 1 ");
				values.put("userId", qCommond.getUserId());
        		   //班级id
        		  if(PropertyUtil.objectNotEmpty(qCommond.getSearchClassId())){
        			   sql.append(" and (tcc.CLASS_ID = :classId ) ");
        			   values.put("classId", qCommond.getSearchClassId().trim());
        		   }
        		  //是否配置了问卷
        		  if(PropertyUtil.objectNotEmpty(qCommond.getExistQuestionnaire())){
        			  sql.append(" and q.questionnaire_name is not null ");
        		  }
        		  if(PropertyUtil.objectNotEmpty(qCommond.getSearchCourseName())){
        			  sql.append(" and tcc.course_name like:courseName ");
        			  values.put("courseName", qCommond.getSearchCourseName());
        		  }
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		//拼写排序的sql begin
		List<SortInfoBean> sortInfoList = qCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			sql.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
			sql.append(" b");
		} else {
			sql.append(" order by tcc.COURSE_NUM asc) b");
		}
		defaultQueryConditionBuilder.setResultObject(ClassCourseQueryBean.class);
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		//查询总数量
		Long count = this.count(queryCondition);
		int firstResult = qCommond.calculate(count.longValue());
		//拼写排序的sql end
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		IPagingQueryCondition queryCondition1 = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition1.setParameters(values);
		
		return (List<ClassCourseQueryBean>) this.findList(queryCondition1,firstResult, qCommond.getLimit());
	}
	
	/**
	 * 删除课程问卷
	 * @param validCommond
	 * @param request
	 * @param response
	 * @param resultState
	 * @throws Exception
	 * @author RongSL
	 * @createDate 2016-5-24
	 */
	@Override
	public void deleteCourseQuestionnaire(ClassCourseValidCommond validCommond){
		String sql="delete from questionnaire_survey_object where survey_object_id in(:courseIDs)";
		Map<String,Object> params=new HashMap<String,Object>();
		params.put("courseIDs", validCommond.getCourseIds());
		this.executeUpdate(QueryStringType.SQL, sql, params);
		//TODO 删除问卷数据
		deleteCourseQuestionnaireAnswer(validCommond);
	}
	
	//删除课程问卷相关答案
	public void deleteCourseQuestionnaireAnswer(ClassCourseValidCommond validCommond){
		String sql="delete from questionnaire_user_answer where class_id  in  (:courseids)";
		Map<String,Object> params=new HashMap<String,Object>();
		params.put("courseids", validCommond.getCourseIds());
		this.executeUpdate(QueryStringType.SQL, sql, params);
	}
}
